﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;

namespace EFCore.Extensions
{
    public static class EFCoreExtentions
    {
        /// <summary>
        /// 扩展方法
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private static IEnumerable<dynamic> ExecuteSqlQuery(this DbContext dbContext, string cmdText, CommandType cmdType= CommandType.Text, params SqlParameter[] parameters)
        {
            using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
            {
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open(); //打开连接
                }
                //处理输入参数
                //foreach (var p in parameters)
                //{
                //var dbPara = cmd.CreateParameter(); //创建参数
                //dbPara.ParameterName = p.ParameterName;
                //dbPara.DbType = p.DbType;
                //dbPara.Value = p.Value;
                //dbPara.Size = p.Size;
                //dbPara.Direction = p.Direction;
                //cmd.Parameters.Add(dbPara);
                //}

                //以上写法有bug，所以按以下的写：
                cmd.Parameters.AddRange(parameters);

                //执行命令，读取器读取数据
                using(var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        IDictionary<string, object> row = new ExpandoObject(); //实例化一个动态可扩展对象
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            row.Add(dataReader.GetName(i), dataReader[i]);
                        }
                        yield return row;
                    }
                }
            }
        }

        /// <summary>
        /// 执行Sql获取列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbContext"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static List<T> Query<T>(this DbContext dbContext, string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] parameters)
        {
            var dynamicObject =  dbContext.ExecuteSqlQuery(cmdText, cmdType, parameters);
            var json = JsonConvert.SerializeObject(dynamicObject);
            var list = JsonConvert.DeserializeObject<List<T>>(json);
            return list;
        }

        /// <summary>
        /// 执行增、删、改的命令
        /// </summary>
        /// <param name="dbContext"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int Execute(this DbContext dbContext, string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] parameters)
        {
            //1. 创建连接对象
            using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
            {
                //接下来把异常处理加入
                try
                {
                    cmd.CommandText = cmdText;
                    cmd.CommandType = cmdType;
                    if (cmd.Connection.State != ConnectionState.Open)
                    {
                        cmd.Connection.Open(); //打开连接
                    }
                    //处理输入参数
                    foreach (var p in parameters)
                    {
                        var dbPara = cmd.CreateParameter(); //创建参数
                        dbPara.ParameterName = p.ParameterName;
                        dbPara.DbType = p.DbType;
                        dbPara.Value = p.Value;
                        cmd.Parameters.Add(dbPara);
                    }

                    int result = cmd.ExecuteNonQuery();   //执行增删改命令
                    return result;
                }
                catch (Exception ex)
                {
                    throw ;
                }
            }
        }
    }
}
